package com.project.andeper.paperproject;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.wyk.greendaodemo.greendao.gen.DaoMaster;
import com.wyk.greendaodemo.greendao.gen.DaoSession;
import com.wyk.greendaodemo.greendao.gen.HourData;
import com.wyk.greendaodemo.greendao.gen.HourDataDao;
import com.wyk.greendaodemo.greendao.gen.MonthData;
import com.wyk.greendaodemo.greendao.gen.MonthDataDao;
import com.wyk.greendaodemo.greendao.gen.User;
import com.wyk.greendaodemo.greendao.gen.UserDao;

import org.greenrobot.greendao.query.QueryBuilder;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by 15888 on 2016/9/22.
 */
public class DBManager {
    //数据库名字
    private final static String dbName = "paperProject_db";
    //管理类实例
    private static DBManager mInstance;
    private DaoMaster.DevOpenHelper openHelper;
    private Context context;

    public DBManager(Context context){
        this.context = context;
        openHelper = new DaoMaster.DevOpenHelper(context,dbName,null);
    }

    //获取单例引用
    public static DBManager getInstance(Context context){
        if(mInstance == null){
            synchronized (DBManager.class){
                if(mInstance ==null){
                    mInstance = new DBManager(context);
                }
            }
        }
        return mInstance;
    }

    //获取可读数据库
    private SQLiteDatabase getReadableDatabase(){
        if(openHelper == null){
            openHelper = new DaoMaster.DevOpenHelper(context,dbName,null);
        }
        SQLiteDatabase db = openHelper.getReadableDatabase();
        return db;
    }

    //获取可写数据库
    private SQLiteDatabase getWritableDatabase() {
        if (openHelper == null) {
            openHelper = new DaoMaster.DevOpenHelper(context, dbName, null);
        }
        SQLiteDatabase db = openHelper.getWritableDatabase();
        return db;
    }
    //插入数据
    public void insertUser(User user){
        DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        UserDao userDao = daoSession.getUserDao();
        userDao.insert(user);
    }
    //插入用户集合
    public void insertUserList(List<User> users) {
        if (users == null || users.isEmpty()) {
            return;
        }
        DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        UserDao userDao = daoSession.getUserDao();
        userDao.insertInTx(users);
    }
    //删除用户
    public void deleteUser(User user) {
        DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        UserDao userDao = daoSession.getUserDao();
        userDao.delete(user);
    }
    //更新用户
    public void updateUser(User user) {
        DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        UserDao userDao = daoSession.getUserDao();
        userDao.update(user);
    }

    //查询用户
    public List<User> queryUserList() {
        DaoMaster daoMaster = new DaoMaster(getReadableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        UserDao userDao = daoSession.getUserDao();
        QueryBuilder<User> qb = userDao.queryBuilder();
        List<User> list = qb.list();
        return list;
    }
    //按ID查询用户
    public List<User> queryUserList(Long id) {
        DaoMaster daoMaster = new DaoMaster(getReadableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        UserDao userDao = daoSession.getUserDao();
        QueryBuilder<User> qb = userDao.queryBuilder();
        qb.where(UserDao.Properties.Id.gt(id)).orderAsc(UserDao.Properties.Id);
        List<User> list = qb.list();
        return list;
    }
    //按用户名、密码查询用户
    public List<User> queryUserList(String name,String pwd){
        DaoMaster daoMaster = new DaoMaster(getReadableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        UserDao userDao = daoSession.getUserDao();
        QueryBuilder<User> qb = userDao.queryBuilder();
        qb.where(UserDao.Properties.Username.eq(name),UserDao.Properties.Password.eq(pwd));
        List<User> list = qb.list();
        return list;
    }

    //查询按月数据
    public List<MonthData> queryMonthData(){
        DaoMaster daoMaster = new DaoMaster(getReadableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        MonthDataDao monthDataDao = daoSession.getMonthDataDao();
        QueryBuilder<MonthData> qb = monthDataDao.queryBuilder();
        List<MonthData> list = qb.list();
        return list;
    }

    //查询按小时数据
    public List<HourData> queryHourData(){
        DaoMaster daoMaster = new DaoMaster(getReadableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        HourDataDao hourDataDao = daoSession.getHourDataDao();
        QueryBuilder<HourData> qb = hourDataDao.queryBuilder();
        List<HourData> list = qb.list();
        return list;
    }

    public void insertMonthDataList(List<MonthData> monthDatas) {
        if (monthDatas == null || monthDatas.isEmpty()) {
            return;
        }
        DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        MonthDataDao monthDataDao = daoSession.getMonthDataDao();
        monthDataDao.insertInTx(monthDatas);
    }

    public void insertHourDataList(List<HourData> hourDatas){
        if (hourDatas == null || hourDatas.isEmpty()) {
            return;
        }
        DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        HourDataDao hourDataDao = daoSession.getHourDataDao();
        hourDataDao.insertInTx(hourDatas);
    }


    public void insertHourData(HourData hourData){
        DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        HourDataDao hourDataDao = daoSession.getHourDataDao();
        hourDataDao.insert(hourData);
    }

    public void deleteHourDate(HourData hourData){
        DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        HourDataDao hourDataDao = daoSession.getHourDataDao();
        hourDataDao.delete(hourData);
    }

    // private static final String SQL_DISTINCT_ENAME = "SELECT DISTINCT "+EmpDao.Properties.EName.columnName+" FROM "+EmpDao.TABLENAME;
    public List<String> searchField(String field){
        String sql = "";
        DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        MonthDataDao monthDataDao = daoSession.getMonthDataDao();
        switch (field){
            case "number":
                sql = "SELECT DISTINCT "+MonthDataDao.Properties.Number.columnName + " FROM " + MonthDataDao.TABLENAME;
                Log.e("tag",sql);
                break;
            case "machine":
                sql = "SELECT DISTINCT "+MonthDataDao.Properties.Machine.columnName + " FROM " + MonthDataDao.TABLENAME;
                break;
            case "variety":
                sql = "SELECT DISTINCT "+MonthDataDao.Properties.Variety.columnName + " FROM " + MonthDataDao.TABLENAME;
                break;
            case "department":
                sql = "SELECT DISTINCT "+MonthDataDao.Properties.Department.columnName + " FROM " + MonthDataDao.TABLENAME;
                break;
            case "userFor":
                sql = "SELECT DISTINCT "+MonthDataDao.Properties.UseFor.columnName + " FROM " + MonthDataDao.TABLENAME;
                break;
            case "useType":
                sql = "SELECT DISTINCT "+MonthDataDao.Properties.UseType.columnName + " FROM " + MonthDataDao.TABLENAME;
                break;
        }
        List<String> result = new ArrayList<String>();
        Cursor c = daoSession.getDatabase().rawQuery(sql, null);
        try{
            if (c.moveToFirst()) {
                do {
                    result.add(c.getString(0));
                } while (c.moveToNext());
            }
        } finally {
            c.close();
        }
        return result;
    }

    public List<Integer> getElectric(String field,String value){
        List<MonthData> monthDatas = new ArrayList<>();
        DaoMaster daoMaster = new DaoMaster(getWritableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        MonthDataDao monthDataDao = daoSession.getMonthDataDao();
        switch (field){
            case "number":
                monthDatas = monthDataDao.queryBuilder().where(MonthDataDao.Properties.Number.eq(value.trim())).list();
                break;
            case "machine":
                monthDatas = monthDataDao.queryBuilder().where(MonthDataDao.Properties.Machine.eq(value)).list();
                break;
            case "variety":
                monthDatas = monthDataDao.queryBuilder().where(MonthDataDao.Properties.Variety.eq(value)).list();
                break;
            case "department":
                monthDatas = monthDataDao.queryBuilder().where(MonthDataDao.Properties.Department.eq(value)).list();
                break;
            case "userFor":
                monthDatas = monthDataDao.queryBuilder().where(MonthDataDao.Properties.UseFor.eq(value)).list();
                break;
            case "useType":
                monthDatas = monthDataDao.queryBuilder().where(MonthDataDao.Properties.UseType.eq(value)).list();
                break;
        }
        int[] res = new int[10];
        for (MonthData monthData:monthDatas) {
            res[monthData.getMonth()] += monthData.getMileage();
        }
        ArrayList<Integer> result = new ArrayList<>();
        for (int i = 1;i<= 9;i++){
            result.add(res[i]);
        }
        return  result;
    }

    //按时刻查询用电量
    public List<HourData> queryHourData(int time){
        DaoMaster daoMaster = new DaoMaster(getReadableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        HourDataDao hourDataDao = daoSession.getHourDataDao();
        QueryBuilder<HourData> qb = hourDataDao.queryBuilder();
        qb.where(HourDataDao.Properties.Hour.eq(time));
        List<HourData> list = qb.list();
        return list;
    }


    //按编号查询用电量
    public List<HourData> queryHourData(String number){
        DaoMaster daoMaster = new DaoMaster(getReadableDatabase());
        DaoSession daoSession = daoMaster.newSession();
        HourDataDao hourDataDao = daoSession.getHourDataDao();
        QueryBuilder<HourData> qb = hourDataDao.queryBuilder();
        qb.where(HourDataDao.Properties.Number.eq(number));
        List<HourData> list = qb.list();
        return list;
    }
}
